﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Wicresoft.UnifyShow.Business;
using Oracle.DataAccess.Client;
using System.Data;

namespace Wicresoft.UnifyShow.BusinessImpl
{
    public class SiteBranch : Basic, ISiteBranch
    {
        private const string selectALL = "select * from sitebranch";
        private const string sqlGetSiteAlertCount = "SELECT b.*,s.ID SiteID FROM (SELECT COUNT(*) ReportCount,s.ALIAS SiteName ,d.TYPE HardwareType FROM alert a " +
"INNER JOIN device d ON d.ID = a.deviceid INNER JOIN sitebranch s ON  d.siteid=s.ID WHERE a.status='OPEN' " +
"GROUP BY s.ALIAS,d.TYPE) b INNER JOIN sitebranch s ON b.sitename=s.ALIAS";
        private const string sqlGetSiteDeviceCount = "SELECT b.*,s.ID SiteID FROM (SELECT COUNT(*) ReportCount,s.ALIAS SiteName,d.TYPE HardwareType FROM device d "
+ "INNER JOIN sitebranch s ON s.ID = d.siteid GROUP BY s.ALIAS,d.TYPE) b INNER JOIN sitebranch s ON b.sitename=s.ALIAS";
        private const string sqlGetSiteAlertCountBySiteID = "SELECT d.TYPE, d.ID,s.id siteid,s.alias sitename, (SELECT COUNT(id) FROM alert a WHERE a.deviceid=d.id AND  a.status='OPEN') AlertCount FROM device d " +
" INNER JOIN sitebranch s ON s.ID = d.siteid  WHERE d.siteid=:siteid";
        public Wicresoft.UnifyShow.Business.SiteBranchModel GetSiteBranchByID(int id)
        {
            QueryParameter[] attParam = new QueryParameter[] {
                        new QueryParameter(){ OracleDbColumnName="ID", OracleDbColumnType=OracleDbType.Int32,
                        OracleDbColumnValue=id,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where},
                    };
            List<SiteBranchModel> x = ModelHelper<SiteBranchModel>.GetModel_By_QueryParams(attParam, null, null, null, oda);
            return ModelHelper<SiteBranchModel>.GetModel_By_QueryParams(attParam, null, null, null, oda).FirstOrDefault();
        }

        public List<SiteBranchModel> GetSiteALLBranch()
        {
            return ModelHelper<SiteBranchModel>.GetModel_By_DirectSql(selectALL, null, oda);
        }

        public List<SiteBranchModel> GetSiteBranchByUserID(int userid)
        {
            string sqlStr = string.Empty;
            sqlStr = "select a.* from sitebranch a, usersite b, user_x c where a.id = b.siteid and b.userid = c.id and (b.disabled=0 or b.disabled is null) and b.userid=:userid";
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            cmdParms.Add(new OracleParameter("userid", OracleDbType.Int32, userid, ParameterDirection.Input));
            OracleDataReader odr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<SiteBranchModel>.GetModel_By_OracleDataReader(odr);
        }

        public List<SiteBranchModel> GetSiteBranch_GJByUserID(int userid)
        {
            string sqlStr = string.Empty;
            sqlStr = "select a.* from sitebranch a, usersite_gj b, user_x c where a.id = b.siteid and b.userid = c.id and (b.disabled=0 or b.disabled is null) and b.userid=:userid";
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            cmdParms.Add(new OracleParameter("userid", OracleDbType.Int32, userid, ParameterDirection.Input));
            OracleDataReader odr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<SiteBranchModel>.GetModel_By_OracleDataReader(odr);
        }


        public IList<SiteAndCountModel> GetSiteDeviceCount()
        {
            return ModelHelper<SiteAndCountModel>.GetModel_By_DirectSql(sqlGetSiteDeviceCount, null, oda);
        }

        public IList<DeviceAndAlertCountModel> GetSiteAlertCount()
        {
            return ModelHelper<DeviceAndAlertCountModel>.GetModel_By_DirectSql(sqlGetSiteAlertCount, null, oda);
        }

        public IList<SiteAndCountModel> GetSiteDeviceCountBySystemName(string systemname)
        {
            string sql = string.Concat(" SELECT b.*,s.ID SiteID FROM (SELECT COUNT(*) ReportCount,s.ALIAS SiteName,d.TYPE HardwareType FROM device d INNER JOIN sitebranch s ON s.ID = d.siteid  ",
                    " where d.BusinessSystemId in (select id from BusinessSystem t4 where t4.name = :systemname) GROUP BY s.ALIAS,d.TYPE) b INNER JOIN sitebranch s ON b.sitename=s.ALIAS ");
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            cmdParms.Add(new OracleParameter("systemname", OracleDbType.NVarchar2, systemname, ParameterDirection.Input));
            return ModelHelper<SiteAndCountModel>.GetModel_By_DirectSql(sql, cmdParms, oda);
        }

        public IList<SiteAndCountModel> GetSiteAlertCountBySystemName(string systemname)
        {
            string sql = string.Concat("SELECT b.*,s.ID SiteID FROM ( SELECT COUNT(*) ReportCount,s.ALIAS SiteName ,d.TYPE HardwareType FROM alert a INNER JOIN device d ON d.ID = a.deviceid INNER JOIN sitebranch s ON  d.siteid=s.ID ",
                   " WHERE a.status='OPEN' and d.BusinessSystemId in (select id from BusinessSystem t4 where t4.name = :systemname) GROUP BY s.ALIAS,d.TYPE ) b INNER JOIN sitebranch s ON b.sitename=s.ALIAS ");
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            cmdParms.Add(new OracleParameter("systemname", OracleDbType.NVarchar2, systemname, ParameterDirection.Input));
            return ModelHelper<SiteAndCountModel>.GetModel_By_DirectSql(sql, cmdParms, oda);
        }


        IList<SiteAndCountModel> ISiteBranch.GetSiteAlertCount()
        {
            throw new NotImplementedException();
        }

        public IList<DeviceAndAlertCountModel> GetSiteAlertCountBySiteID(int siteID)
        {
            return ModelHelper<DeviceAndAlertCountModel>.GetModel_By_DirectSql(sqlGetSiteAlertCountBySiteID, new List<OracleParameter>()
                 {
                     new OracleParameter(){ ParameterName="siteid",OracleDbType=OracleDbType.Decimal,/*字符串类型必须有Size*/Size=20,Value=siteID}
                 },
oda);
        }
    }
}
